<?php
/**
 * Created by PhpStorm.
 * User: syzhou
 * Date: 2019-08-29
 * Time: 16:31
 */

namespace App\Services;


use App\Consts;
use App\MeetingReviewProject;
use App\Project;
use App\ReviewerGroupProject;
use App\ReviewerScoreProject;
use App\User;
use App\WorkflowPhase;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use PharIo\Manifest\Application;
use function foo\func;

class ManageLargeGroupService
{

    private $workflowSvc;

    public function __construct()
    {
        $this->workflowSvc = new WorkflowService();
    }

    /**
     * @param $largeGroupId
     * @param $awards
     * @return array[array]
     * @throws \Exception
     */
    public function adminAutoSetAwardLevel($largeGroupId, $awards)
    {
        $workflowSvc = new WorkflowService();
        if ($workflowSvc->notAtPhases([WorkflowService::PHASE_PLAN_AWARD], $largeGroupId)) {
            return [
                'ok' => false,
                'code' => 3,
                'msg' => '现在不是设置授奖等级阶段，不能执行该操作'
            ];
        }

        $largeGroup = DB::table('large_groups')->find($largeGroupId);
        $largeGroupAttrs = json_decode($largeGroup->attrs, true);

        // 设置各等级奖项的分数线
        $awardName2NewAward = [];
        foreach ($awards as $award) {
            $awardName2NewAward[$award['name']] = $award;
        }
        foreach ($largeGroupAttrs['award']['awards'] as &$award) {
            $awardName = $award['name'];
            $award['min_score'] = $awardName2NewAward[$awardName]['min_score'];
        }
        unset($award);

        // 设置项目的计划授奖等级
        $inTransaction = false;
        try {
            DB::beginTransaction();
            $inTransaction = true;
            DB::table('large_groups')
                ->where('id', $largeGroupId)
                ->update(['attrs' => json_encode($largeGroupAttrs, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT)]);
            $statusIds = [Project::STATUS_PLAN_AWARD_TO_PLAN_AWARD, Project::STATUS_PLAN_AWARD_TO_SUBMIT_PLAN];
            $maxScore = 100;
            $awards = array_merge($largeGroupAttrs['award']['awards'], [['name' => '', 'min_score' => 0]]);
            $isFirstAward = true;
            foreach ($awards as $award) {
                $minScore = $award['min_score'];
                Project::where('large_group_id', $largeGroupId)
                    ->whereIn('status_id', $statusIds)
                    ->where('avg_score', '>=', $minScore)
                    ->where('avg_score', $isFirstAward ? '<=' : '<', $maxScore)
                    ->update([
                        'status_id' => Project::STATUS_PLAN_AWARD_TO_SUBMIT_PLAN,
                        'planned_award_level' => $award['name'],
                    ]);
                $maxScore = $minScore;
                $isFirstAward = false;
            }
            DB::commit();
            return [
                'ok' => true,
                'code' => 0,
                'msg' => 'ok',
            ];
        } catch (\Exception $e) {
            Log::error('', ['exception' => $e]);
            if ($inTransaction) {
                try {
                    DB::rollBack();
                } catch (\Exception $e2) {

                }
            }
            return [
                'ok' => false,
                'code' => 2,
                'msg' => '数据库错误',
            ];
        }
    }

    public function adminUpdatePlannedAwardLevel($projectId, $plannedAwardLevel)
    {
        $largeGroupId = Project::where('id', $projectId)->value('large_group_id');
        $workflowSvc = new WorkflowService();
        if ($workflowSvc->notAtPhases([WorkflowService::PHASE_PLAN_AWARD], $largeGroupId)) {
            return [
                'ok' => false,
                'code' => 3,
                'msg' => '现在不是设置授奖等级阶段，不能执行该操作'
            ];
        }
        Project::where('id', $projectId)->update([
            'status_id' => Project::STATUS_PLAN_AWARD_TO_SUBMIT_PLAN,
            'planned_award_level' => $plannedAwardLevel,
        ]);
        return [
            'ok' => true,
            'code' => 0,
            'msg' => 'ok',
        ];
    }

    /**
     * 管理员提交设置授奖等级的结果
     * @param $largeGroupId
     * @return array
     * @throws \Exception
     */
    public function adminSubmitPlanAwardResult($largeGroupId)
    {
        $workflowSvc = new WorkflowService();
        if ($workflowSvc->notAtPhases([WorkflowService::PHASE_PLAN_AWARD], $largeGroupId)) {
            return [
                'ok' => false,
                'code' => 3,
                'msg' => '现在不是设置授奖等级阶段，不能执行该操作'
            ];
        }
        $toPlanAwardProjectCount = Project::where('large_group_id', $largeGroupId)
            ->where('status_id', Project::STATUS_PLAN_AWARD_TO_PLAN_AWARD)
            ->count();
        if ($toPlanAwardProjectCount > 0) {
            return [
                'ok' => false,
                'code' => 4,
                'msg' => "还有 $toPlanAwardProjectCount 个项目未指定授奖等级，不能执行该操作"
            ];
        }

        $toVoteProjectIds = Project::where('large_group_id', $largeGroupId)
            ->where('status_id', Project::STATUS_PLAN_AWARD_TO_SUBMIT_PLAN)
            ->where('planned_award_level', '!=', '')
            ->pluck('id');
        $commissioner_project_vote_records = [];
        if (!empty($toVoteProjectIds)) {
            $workingCommissionerIds = DB::table('working_commissioners')->pluck('commissioner_id');
            foreach ($toVoteProjectIds as $projectId) {
                foreach ($workingCommissionerIds as $commissionerId) {
                    $commissioner_project_vote_records[] = [
                        'project_id' => $projectId,
                        'commissioner_id' => $commissionerId,
                        'large_group_id' => $largeGroupId,
                        'result' => Consts::VOTE_RESULT_UNVOTED,
                        'remark' => '',
                    ];
                }
            }
        }
        $inTransaction = false;
        try {
            DB::beginTransaction();
            $inTransaction = true;
            Project::where('large_group_id', $largeGroupId)
                ->where('status_id', Project::STATUS_PLAN_AWARD_TO_SUBMIT_PLAN)
                ->where('planned_award_level', '!=', '')
                ->update(['status_id' => Project::STATUS_VOTE_TO_VOTE]);
            Project::where('large_group_id', $largeGroupId)
                ->where('status_id', Project::STATUS_PLAN_AWARD_TO_SUBMIT_PLAN)
                ->where('planned_award_level', '=', '')
                ->update(['status_id' => Project::STATUS_PLAN_AWARD_REJECTED]);
            if (!empty($commissioner_project_vote_records)) {
                DB::table('commissioner_project_votes')->insert($commissioner_project_vote_records);
            }
            $transferPhaseResult = $workflowSvc->transferPhase(WorkflowService::PHASE_PLAN_AWARD,
                WorkflowService::PHASE_VOTE, $largeGroupId, $inTransaction);
            if (!$transferPhaseResult['ok']) {
                DB::rollBack();
                return [
                    'ok' => false,
                    'code' => 5,
                    'msg' => "阶段迁移时出错。(code={$transferPhaseResult['code']}, msg={$transferPhaseResult['msg']})",
                ];
            }
            DB::commit();
            return [
                'ok' => true,
                'code' => 0,
                'msg' => 'ok',
            ];
        } catch (\Exception $e) {
            Log::error('', ['exception' => $e]);
            if ($inTransaction) {
                try {
                    DB::rollBack();
                } catch (\Exception $e2) {

                }
            }
            return [
                'ok' => false,
                'code' => 2,
                'msg' => '数据库错误',
            ];
        }
    }


    public function adminSubmitPrepareDefenseResult($largeGroupId)
    {
        if ($this->workflowSvc->notAtPhase(WorkflowService::PHASE_PREPARE_DEFENSE, $largeGroupId)) {
            return [
                'ok' => false,
                'code' => 3,
                'msg' => "现在不是准备答辩阶段，不能执行该操作",
            ];
        }
        $candidateDefenseProjects = DB::table('candidate_defense_projects')->where('large_group_id', $largeGroupId)->get();
        $selectedProjectIds = [];
        $unselectedProjectIds = [];
        foreach ($candidateDefenseProjects as $project) {
            $projectId = $project->project_id;
            if ($project->is_selected == 1) {
                $selectedProjectIds[] = $projectId;
            } else {
                $unselectedProjectIds[] = $projectId;
            }
        }

        $defenseProjects = [];
        foreach ($selectedProjectIds as $projectId) {
            $defenseProjects[] = [
                'project_id' => $projectId,
                'large_group_id' => $largeGroupId,
            ];
        }

        $inTransaction = false;
        try {
            DB::beginTransaction();
            $inTransaction = true;
            if (!empty($unselectedProjectIds)) {
                Project::whereIn('id', $unselectedProjectIds)->update(['status_id' => Project::STATUS_AWARD_AWARD]);
            }
            if (!empty($selectedProjectIds)) {
                Project::whereIn('id', $selectedProjectIds)->update(['status_id' => Project::STATUS_DEFENSE_TO_REVIEW]);
            }
            if (!empty($defenseProjects)) {
                DB::table('defense_projects')->insert($defenseProjects);
            }
            $transferPhaseResult = $this->workflowSvc->transferPhase(WorkflowService::PHASE_PREPARE_DEFENSE, WorkflowService::PHASE_DEFENSE, $largeGroupId, $inTransaction);
            if (!$transferPhaseResult['ok']) {
                DB::rollBack();
                return [
                    'ok' => false,
                    'code' => 5,
                    'msg' => "阶段迁移时出错(code={$transferPhaseResult['code']}, msg={$transferPhaseResult['msg']}) ",
                ];
            }
            DB::commit();
            return [
                'ok' => true,
                'code' => 0,
                'msg' => 'ok',
            ];
        } catch (\Exception $e) {
            Log::error('', ['exception' => $e]);
            if ($inTransaction) {
                try {
                    DB::rollBack();
                } catch (\Exception $e) {
                    Log::error('', ['exception' => $e]);
                }
            }
            return [
                'ok' => false,
                'code' => 2,
                'msg' => '数据库错误',
            ];
        }
    }

    public function getLargeGroupById($largeGroupId)
    {
        $largeGroup = DB::table('large_groups')->find($largeGroupId);
        $largeGroup->attrs = json_decode($largeGroup->attrs);
        return [
            'ok' => true,
            'code' => false,
            'large_group' => $largeGroup,
        ];
    }

    /**
     * 组长审核某项目的专家组评审结果
     * @param $projectId
     * @param $reviewResult string 组长审核结果, ReviewerGroupProject::LEADER_REVIEW_RESULT_XXX 常量
     * @param $reviewRemark string 组长审核理由
     * @return array
     */
    public function leaderReviewExpertReviewResult($projectId, $reviewResult, $reviewRemark)
    {
        $inTransaction = false;
        try {
            $reviewerGroupProject = ReviewerGroupProject::where('project_id', $projectId)->first();
            $largeGroupId = $reviewerGroupProject->large_group_id;
            if ($this->workflowSvc->notAtPhases([WorkflowService::PHASE_EXPERT_REVIEW], $largeGroupId)) {
                return [
                    'ok' => false,
                    'code' => 3,
                    'msg' => '现在不是专家评审阶段，不能执行该操作',
                ];
            }
            if ($reviewerGroupProject->score_status !== ReviewerGroupProject::SCORE_STATUS_TO_LEADER_REVIEW) {
                return [
                    'ok' => false,
                    'code' => 4,
                    'msg' => '当前该项目的专家组评分状态不是' . ReviewerGroupProject::SCORE_STATUS_TO_LEADER_REVIEW . '，不能执行该操作',
                ];
            }
            $reviewRemark = trim($reviewRemark);
            if ($reviewResult == ReviewerGroupProject::LEADER_REVIEW_RESULT_REJECTED && strlen($reviewRemark) == 0) {
                return [
                    'ok' => false,
                    'code' => 5,
                    'msg' => "必须填写不通过的理由",
                ];
            }
            if ($reviewResult == ReviewerGroupProject::LEADER_REVIEW_RESULT_PASSED) {
                $reviewerGroupScoreStatus = ReviewerGroupProject::SCORE_STATUS_LEADER_REVIEW_PASSED;
                $projectStatus = Project::STATUS_EXPERT_REVIEW_LEADER_REVIEW_PASSED;
            } else {
                $reviewerGroupScoreStatus = ReviewerGroupProject::SCORE_STATUS_LEADER_REVIEW_REJECTED;
                $projectStatus = Project::STATUS_EXPERT_REVIEW_LEADER_REVIEW_REJECTED;
            }
            DB::beginTransaction();
            $inTransaction = true;
            $reviewerGroupProject->update([
                'score_status' => $reviewerGroupScoreStatus,
                'large_group_leader_review_result' => $reviewResult,
                'large_group_leader_review_remark' => $reviewRemark,
            ]);
            Project::where('id', $projectId)->update(['status_id' => $projectStatus]);
            DB::commit();
            return [
                'ok' => true,
                'code' => 0,
                'msg' => 'ok',
            ];
        } catch (\Exception $e) {
            Log::error('', ['exception' => $e]);
            if ($inTransaction) {
                try {
                    DB::rollBack();
                } catch (\Exception $e) {
                    Log::error('', ['exception' => $e]);
                }
            }
            return [
                'ok' => false,
                'code' => 2,
                'msg' => '数据库错误',
            ];
        }
    }

    public function leaderSubmitExpertReviewResult($largeGroupId)
    {
        if ($this->workflowSvc->notAtPhases([WorkflowService::PHASE_EXPERT_REVIEW], $largeGroupId)) {
            return [
                'ok' => false,
                'code' => 3,
                'msg' => '现在不是专家评审阶段，不能执行该操作',
            ];
        }

        /**
         * @edit 2020-06-10
         * @需求：STATUS_EXPERT_REVIEW_TO_LEADER_REVIEW 状态已废弃
         * 网评阶段状态分为：
         * STATUS_EXPERT_REVIEW_TO_SCORE        待评分
         * STATUS_EXPERT_REVIEW_SCORE_FINISHED  已评分
         * STATUS_EXPERT_REVIEW_REJECTED        落选会评
         */
        $scoreUnfinishedProjectCount = Project::where('large_group_id', $largeGroupId)
            ->where('status_id', Project::STATUS_EXPERT_REVIEW_TO_SCORE)
            ->count();
        /**
         * @end
         */

        if ($scoreUnfinishedProjectCount > 0) {
            return [
                'ok' => false,
                'code' => 4,
                'msg' => "还有 $scoreUnfinishedProjectCount 个项目未完成专家评审，不能执行该操作",
            ];
        }

        /**
         * @add
         * @time 2020-06-12
         */
        //按照项目评分设定项目排名
        $reviewer_projects = DB::table('reviewer_group_projects as r')
            ->where('r.large_group_id', $largeGroupId)
            ->leftJoin('applications as a', 'a.id', '=', 'r.project_id')
            ->where('a.status_id', Project::STATUS_MEETING_REVIEW_TO_REVIEW)
            ->orderBy('r.avg_score', 'desc')
            ->select('r.project_id')
            ->get()
            ->toArray();

        if (empty($reviewer_projects)) {
            return [
                'ok' => false,
                'code' => 4,
                'msg' => '暂无进入会评项目',
            ];
        }

        $meeting_initial_ranking_sql = 'set meeting_initial_ranking = case id ';
        $meeting_ranking_sql = 'meeting_ranking = case id ';
        $projectIds = [];
        $insertingMeetingReviewProjects = [];
        foreach ($reviewer_projects as $key => $val) {
            //排名sql生成
            $key++;
            $projectIds[] = $val->project_id;
            $meeting_initial_ranking_sql .= " WHEN {$val->project_id} THEN $key ";

            $meeting_ranking_sql .= " WHEN {$val->project_id} THEN $key ";

            //生成加入会评项目组信息
            $insertingMeetingReviewProjects[] = [
                'project_id' => $val->project_id,
                'large_group_id' => $largeGroupId,
                'review_status' => MeetingReviewProject::REVIEW_STATUS_TO_REVIEW,
                'review_remark' => '',
            ];
        }

        $projectIds = implode(',', $projectIds);
        $updateProjectSql = "update `applications` {$meeting_initial_ranking_sql} end,{$meeting_ranking_sql} end where id in ($projectIds)";
        /**
         * @end
         */


        $inTransaction = false;
        try {
            DB::beginTransaction();
            $inTransaction = true;
            //if (!empty($disputedProjects)) {
            /**
             * 清除之前的数据
             * @start
             */
            DB::table('meeting_review_projects')->where('large_group_id', $largeGroupId)->delete();
            /**
             * @end
             */
            DB::table('meeting_review_projects')->insert($insertingMeetingReviewProjects);
            //Project::whereIn('id', $updatingProjectIds)->update(['status_id' => Project::STATUS_MEETING_REVIEW_TO_REVIEW]);
            //}

            DB::update($updateProjectSql);

            $transferPhaseResult = $this->workflowSvc->transferPhase(
                WorkflowService::PHASE_EXPERT_REVIEW, WorkflowService::PHASE_MEETING_REVIEW,
                $largeGroupId, $inTransaction);
            if (!$transferPhaseResult['ok']) {
                DB::rollBack();
                return [
                    'ok' => false,
                    'code' => 5,
                    'msg' => "阶段迁移时出错(code={$transferPhaseResult['code']}, msg={$transferPhaseResult['msg']}) ",
                ];
            }
            DB::commit();
            return [
                'ok' => true,
                'code' => 0,
                'msg' => 'ok',
            ];
        } catch (\Exception $e) {
            Log::error('', ['exception' => $e]);
            if ($inTransaction) {
                try {
                    DB::rollBack();
                } catch (\Exception $e2) {

                }
            }
            return [
                'ok' => false,
                'code' => 2,
                'msg' => '数据库错误',
            ];
        }
    }

    /**
     * 提交会评
     * @param $largeGroupId
     * @return array
     * @throws \Exception
     * @author: lxy
     * @time: 2020/8/11 7:44 下午
     * @流程:
     */
    public function leaderSubmitMeetingReviewResult($largeGroupId)
    {
        $workflowSvc = new WorkflowService();
        if ($workflowSvc->notAtPhase(WorkflowService::PHASE_MEETING_REVIEW, $largeGroupId)) {
            return [
                'ok' => false,
                'code' => 2,
                'msg' => '现在不是会评阶段，不能执行该操作'
            ];
        }

        $inTransaction = false;
        try {
            DB::beginTransaction();
            $inTransaction = true;
            MeetingReviewProject::where('large_group_id', $largeGroupId)
                ->update(['review_status' => MeetingReviewProject::REVIEW_STATUS_FINISHED]);
            Project::where('large_group_id', $largeGroupId)
                ->where('status_id', Project::STATUS_MEETING_REVIEW_TO_REVIEW)
                ->update(['status_id' => Project::STATUS_MEETING_REVIEW_REVIEW_FINISHED]);
            $endPhaseResult = $this->workflowSvc->endPhase(WorkflowService::PHASE_MEETING_REVIEW, $largeGroupId, $inTransaction);
            if (!$endPhaseResult['ok']) {
                return [
                    'ok' => false,
                    'code' => 3,
                    'msg' => "结束会评阶段时出错。(code={$endPhaseResult['code']}, msg={$endPhaseResult['msg']})",
                ];
            }
            DB::commit();
            $result = [
                'ok' => true,
                'code' => 0,
                'msg' => 'ok',
            ];
        } catch (\Exception $e) {
            if ($inTransaction) {
                try {
                    DB::rollback();
                } catch (\Exception $e2) {
                }
            }
            $result = [
                'ok' => false,
                'code' => 1,
                'msg' => $e->getMessage()
            ];
        }

        return $result;
    }

    public function getManageLargeGroup2Data($largeGroupId)
    {
        $largeGroup = DB::table('large_groups')->find($largeGroupId);
        $largeGroupAttrs = json_decode($largeGroup->attrs);
        $phaseId = WorkflowService::PHASE_PLAN_AWARD_2;
        $statusIds = $this->workflowSvc->getProjectStatusesSincePhase($phaseId);
        $result = [
            'ok' => true,
            'code' => 0,
            'msg' => 'ok',
            'atPhaseSetUndeterminedInterval' => $this->workflowSvc->atPhases([$phaseId], $largeGroupId),
            'hasPhaseSetUndeterminedIntervalBegun' => $this->workflowSvc->hasPhaseBegun($phaseId, $largeGroupId),
            'hasPhaseSetUndeterminedIntervalEnded' => $this->workflowSvc->hasPhaseEnded($phaseId, $largeGroupId),
            'maxAwardRate' => $largeGroupAttrs->award->max_award_rate,
            'undeterminedIntervalBegin' => $largeGroupAttrs->award->undetermined_interval_begin,
            'undeterminedIntervalEnd' => $largeGroupAttrs->award->undetermined_interval_end,
            'projects' => DB::table('applications as a')
                ->where('a.large_group_id', $largeGroupId)
                ->whereIn('a.status_id', $statusIds)
                ->leftJoin('users as b', 'a.user_id', '=', 'b.id')
                ->select('a.id', 'a.project_name', 'b.name as applicant', 'a.avg_score', 'a.ranking_percent', 'planned_award_level')
                ->orderBy('ranking')
                ->get(),
        ];
        return $result;
    }

    public function leaderSetUndeterminedInterval($largeGroupId, $undeterminedIntervalBegin, $undeterminedIntervalEnd)
    {
        if (!$this->workflowSvc->atPhases([WorkflowService::PHASE_PLAN_AWARD_2], $largeGroupId)) {
            return [
                'ok' => false,
                'code' => 3,
                'msg' => '现在不是划定待定区阶段，不能执行该操作'
            ];
        }
        $largeGroup = DB::table('large_groups')->find($largeGroupId);
        $largeGroupAttrs = json_decode($largeGroup->attrs);
        $award = $largeGroupAttrs->award;
        $maxAwardRate = $award->max_award_rate;
        if (!($undeterminedIntervalBegin >= 0 && $undeterminedIntervalBegin <= $undeterminedIntervalEnd
            && $undeterminedIntervalEnd <= $maxAwardRate)) {
            return [
                'ok' => false,
                'code' => 4,
                'msg' => "待定区应满足：0 <= 起始值 <= 结束值 <= $maxAwardRate",
            ];
        }
        $award->undetermined_interval_begin = $undeterminedIntervalBegin;
        $award->undetermined_interval_end = $undeterminedIntervalEnd;
        DB::table('large_groups')
            ->where('id', $largeGroupId)
            ->update(['attrs' => json_encode($largeGroupAttrs, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT)]);
        return [
            'ok' => true,
            'code' => 0,
            'msg' => 'ok',
        ];
    }

    /**
     * 大组长提交设置待定区的结果
     * @param $largeGroupId
     * @return array
     */
    public function leaderSubmitPlanAward2Result($largeGroupId)
    {
        if (!$this->workflowSvc->atPhases([WorkflowService::PHASE_PLAN_AWARD_2], $largeGroupId)) {
            return [
                'ok' => false,
                'code' => 3,
                'msg' => '现在不是划定待定区阶段，不能执行该操作'
            ];
        }

        $inTransaction = false;
        try {
            DB::beginTransaction();
            $inTransaction = true;
            $largeGroup = DB::table('large_groups')->find($largeGroupId);
            $largeGroupAttrs = json_decode($largeGroup->attrs);
            $award = $largeGroupAttrs->award;
            $undeterminedIntervalBegin = $award->undetermined_interval_begin;
            $undeterminedIntervalEnd = $award->undetermined_interval_end;
            Project::where('large_group_id', $largeGroupId)
                ->where('status_id', Project::STATUS_PLAN_AWARD_2_TO_SET_UNDETERMINDED_INTERVAL)
                ->where('ranking_percent', '<', $undeterminedIntervalBegin)
                ->update(['status_id' => Project::STATUS_PLAN_AWARD_2_SKIP_VOTE_1]);
            Project::where('large_group_id', $largeGroupId)
                ->where('status_id', Project::STATUS_PLAN_AWARD_2_TO_SET_UNDETERMINDED_INTERVAL)
                ->whereBetween('ranking_percent', [$undeterminedIntervalBegin, $undeterminedIntervalEnd])
                ->update(['status_id' => Project::STATUS_VOTE_1_TO_VOTE]);
            Project::where('large_group_id', $largeGroupId)
                ->where('status_id', Project::STATUS_PLAN_AWARD_2_TO_SET_UNDETERMINDED_INTERVAL)
                ->where('ranking_percent', '>', $undeterminedIntervalEnd)
                ->update(['status_id' => Project::STATUS_PLAN_AWARD_2_REJECTED]);

            $undeterminedProjectIds = Project::where('large_group_id', $largeGroupId)
                ->where('status_id', Project::STATUS_VOTE_1_TO_VOTE)
                ->pluck('id');
            if (!empty($undeterminedProjectIds)) {
                $commissioner_vote_1_records = [];
                $workingCommissionerIds = DB::table('working_commissioners')->pluck('commissioner_id');
                foreach ($undeterminedProjectIds as $undeterminedProjectId) {
                    foreach ($workingCommissionerIds as $commissionerId) {
                        $commissioner_vote_1_records[] = [
                            'project_id' => $undeterminedProjectId,
                            'commissioner_id' => $commissionerId,
                            'large_group_id' => $largeGroupId,
                            'result' => '',
                            'remark' => '',
                        ];
                    }
                }
                DB::table('commissioner_vote_1')->insert($commissioner_vote_1_records);
            }

            $transferPhaseResult = $this->workflowSvc->transferPhase(WorkflowService::PHASE_PLAN_AWARD_2,
                WorkflowService::PHASE_VOTE_1, $largeGroupId, $inTransaction);
            if (!$transferPhaseResult['ok']) {
                DB::rollBack();
                return [
                    'ok' => false,
                    'code' => 4,
                    'msg' => "阶段迁移时出错。(code={$transferPhaseResult['code']}, msg={$transferPhaseResult['msg']})",
                ];
            }
            DB::commit();
            return [
                'ok' => true,
                'code' => 0,
                'msg' => 'ok',
            ];
        } catch (\Exception $e) {
            Log::error('', ['exception' => $e]);
            if ($inTransaction) {
                try {
                    DB::rollBack();
                } catch (\Exception $e2) {

                }
            }
            return [
                'ok' => false,
                'code' => 2,
                'msg' => '数据库错误',
            ];
        }
    }

    public function getManageLargeGroup3Data($largeGroupId)
    {
        $phaseId = WorkflowService::PHASE_VOTE_1;
        $voteStats = DB::select('select project_id, result, count(*) as count from commissioner_vote_1 where large_group_id = ? group by project_id, result', [$largeGroupId]);
        $projectIdSet = [];
        $projectId2Stats = [];
        foreach ($voteStats as $item) {
            $projectId = $item->project_id;
            $projectIdSet[$projectId] = null;
            if (!isset($projectId2Stats[$projectId])) {
                $projectId2Stats[$projectId] = [
                    Consts::VOTE_RESULT_AGREE => 0,
                    Consts::VOTE_RESULT_DISAGREE => 0,
                    Consts::VOTE_RESULT_UNVOTED => 0,
                ];
            }
            $projectId2Stats[$projectId][$item->result] = $item->count;
        }

        $projects = [];
        if (!empty($projectIdSet)) {
            $projectIds = array_keys($projectIdSet);
            $projects = DB::table('applications as a')
                ->whereIn('a.id', $projectIds)
                ->leftJoin('users as b', 'a.user_id', '=', 'b.id')
                ->select('a.id', 'a.project_name', 'b.name as applicant', 'a.avg_score', 'a.ranking_percent', 'planned_award_level')
                ->orderBy('ranking')
                ->get();
        }
        foreach ($projects as $project) {
            $stats = $projectId2Stats[$project->id];
            $project->agree_count = $stats[Consts::VOTE_RESULT_AGREE];
            $project->disagree_count = $stats[Consts::VOTE_RESULT_DISAGREE];
            $project->remaining_count = $stats[Consts::VOTE_RESULT_UNVOTED];
        }
        $result = [
            'ok' => true,
            'code' => 0,
            'msg' => 'ok',
            'atPhaseVote1' => $this->workflowSvc->atPhases([$phaseId], $largeGroupId),
            'hasPhaseVote1Begun' => $this->workflowSvc->hasPhaseBegun($phaseId, $largeGroupId),
            'hasPhaseVote1Ended' => $this->workflowSvc->hasPhaseEnded($phaseId, $largeGroupId),
            'projects' => $projects,
        ];
        return $result;
    }

    public function leaderSubmitVote1Result($largeGroupId)
    {
        $workflowSvc = new WorkflowService();
        if ($workflowSvc->notAtPhases([WorkflowService::PHASE_VOTE_1], $largeGroupId)) {
            return [
                'ok' => false,
                'code' => 3,
                'msg' => '现在不是待定区投票阶段，不能执行该操作'
            ];
        }
        $unfinishedProjects = Project::where('large_group_id', $largeGroupId)
            ->where('status_id', Project::STATUS_VOTE_1_TO_VOTE)
            ->count();
        if ($unfinishedProjects > 0) {
            return [
                'ok' => false,
                'code' => 4,
                'msg' => "还有 $unfinishedProjects 个项目未完成投票，不能执行该操作",
            ];
        }

        $largeGroup = DB::table('large_groups')->find($largeGroupId);
        $largeGroupAttrs = json_decode($largeGroup->attrs);
        $vote1_min_support_rate = $largeGroupAttrs->award->vote1_min_support_rate;
        $totalVoteCount = DB::table('working_commissioners')->count();
        $passedProjectIds = [];
        $rejectedProjectIds = [];
        $voteStats = DB::select("select project_id, result, count(*) as count from commissioner_vote_1 where large_group_id = ? group by project_id, result", [$largeGroupId]);
        $projectId2AgreeCount = [];
        foreach ($voteStats as $item) {
            $projectId = $item->project_id;
            if (!isset($projectId2AgreeCount[$projectId])) {
                $projectId2AgreeCount[$projectId] = 0;
            }
            if ($item->result === Consts::VOTE_RESULT_AGREE) {
                $projectId2AgreeCount[$projectId] = $item->count;
            }
        }
        foreach ($projectId2AgreeCount as $projectId => $agreeCount) {
            if ($agreeCount * $vote1_min_support_rate[1] >= $vote1_min_support_rate[0] * $totalVoteCount) {
                $passedProjectIds[] = $projectId;
            } else {
                $rejectedProjectIds[] = $projectId;
            }
        }
        $inTransaction = false;
        try {
            DB::beginTransaction();
            $inTransaction = true;
            if (!empty($rejectedProjectIds)) {
                Project::whereIn('id', $rejectedProjectIds)->update(['status_id' => Project::STATUS_VOTE_1_REJECTED]);
            }

            // 通过待定区投票的项目，以及排名领先待定区的项目，都进入终轮投票阶段
            Project::whereIn('id', $passedProjectIds)
                ->orWhere('status_id', Project::STATUS_PLAN_AWARD_2_SKIP_VOTE_1)
                ->update(['status_id' => Project::STATUS_VOTE_TO_VOTE]);

            $vote2ProjectIds = Project::where('large_group_id', $largeGroupId)
                ->where('status_id', Project::STATUS_VOTE_TO_VOTE)
                ->pluck('id');
            if (!empty($vote2ProjectIds)) {
                $commissioner_vote_2_records = [];
                $workingCommissionerIds = DB::table('working_commissioners')->pluck('commissioner_id');
                foreach ($vote2ProjectIds as $projectId) {
                    foreach ($workingCommissionerIds as $commissionerId) {
                        $commissioner_vote_2_records[] = [
                            'project_id' => $projectId,
                            'commissioner_id' => $commissionerId,
                            'large_group_id' => $largeGroupId,
                            'result' => '',
                            'remark' => '',
                        ];
                    }
                }
                DB::table('commissioner_vote_2')->insert($commissioner_vote_2_records);
            }

            $transferPhaseResult = $this->workflowSvc->transferPhase(WorkflowService::PHASE_VOTE_1,
                WorkflowService::PHASE_VOTE, $largeGroupId, $inTransaction);
            if (!$transferPhaseResult['ok']) {
                DB::rollBack();
                return [
                    'ok' => false,
                    'code' => 4,
                    'msg' => "阶段迁移时出错。(code={$transferPhaseResult['code']}, msg={$transferPhaseResult['msg']})",
                ];
            }
            DB::commit();
            return [
                'ok' => true,
                'code' => 0,
                'msg' => 'ok',
            ];
        } catch (\Exception $e) {
            Log::error('', ['exception' => $e]);
            if ($inTransaction) {
                try {
                    DB::rollBack();
                } catch (\Exception $e2) {

                }
            }
            return [
                'ok' => false,
                'code' => 2,
                'msg' => '数据库错误',
            ];
        }
    }

    public function getManageLargeGroupVoteData($largeGroupId)
    {
        $phaseId = WorkflowService::PHASE_VOTE;
        $result = [
            'ok' => true,
            'code' => 0,
            'msg' => 'ok',
            'atPhaseVote2' => $this->workflowSvc->atPhases([$phaseId], $largeGroupId),
            'hasPhaseVote2Begun' => $this->workflowSvc->hasPhaseBegun($phaseId, $largeGroupId),
            'hasPhaseVote2Ended' => $this->workflowSvc->hasPhaseEnded($phaseId, $largeGroupId),
            'projects' => (new ProjectService())->getProjectVoteStats($largeGroupId),
        ];
        return $result;
    }

    public function leaderSubmitVoteResult($largeGroupId)
    {
        $workflowSvc = new WorkflowService();
        if ($workflowSvc->notAtPhases([WorkflowService::PHASE_VOTE], $largeGroupId)) {
            return [
                'ok' => false,
                'code' => 3,
                'msg' => '现在不是投票阶段，不能执行该操作'
            ];
        }
        $unfinishedProjects = Project::where('large_group_id', $largeGroupId)
            ->where('status_id', Project::STATUS_VOTE_TO_VOTE)
            ->count();
        if ($unfinishedProjects > 0) {
            return [
                'ok' => false,
                'code' => 4,
                'msg' => "还有 $unfinishedProjects 个项目未完成投票，不能执行该操作",
            ];
        }

        $largeGroup = DB::table('large_groups')->find($largeGroupId);
        $largeGroupAttrs = json_decode($largeGroup->attrs);
        $projectVoteStats = (new ProjectService())->getProjectVoteStats($largeGroupId);
        $totalVotes = 0;
        if (!empty($projectVoteStats)) {
            $item = $projectVoteStats[0];
            $totalVotes = $item->agree_count + $item->disagree_count + $item->remaining_count;
        }
        $projects = [];
        $candidateDefenseProjects = [];
        foreach ($projectVoteStats as $item) {
            $projectId = $item->id;
            $project = ['id' => $projectId];
            $project['voted_award_level'] = $project['final_award_level'] = $finalAwardLevel =
                (new AwardService())->calcFinalAwardLevelByVote($largeGroupAttrs->award, $item->planned_award_level, $totalVotes, $item->agree_count);
            if ($finalAwardLevel === Consts::AWARD_LEVEL_NONE) {
                $project['status_id'] = Project::STATUS_VOTE_REJECTED;
            } else {
                if ($largeGroup->award_id == Consts::AWARD_TYPE_TALENT) {
                    $project['status_id'] = Project::STATUS_AWARD_AWARD;
                } else {  // 进步奖
                    if ($finalAwardLevel == Consts::PROGRESS_AWARD_LEVEL1) {
                        $project['status_id'] = Project::STATUS_PREPARE_DEFENSE_TO_SELECT;
                        $candidateDefenseProjects[] = [
                            'project_id' => $projectId,
                            'large_group_id' => $largeGroupId,
                            'is_selected' => 0,
                        ];
                    } else {
                        $project['status_id'] = Project::STATUS_AWARD_AWARD;
                    }
                }
            }
            $projects[] = $project;
        }

        $inTransaction = false;
        try {
            DB::beginTransaction();
            $inTransaction = true;
            foreach ($projects as $project) {
                Project::where('id', $project['id'])
                    ->update([
                        'status_id' => $project['status_id'],
                        'final_award_level' => $project['final_award_level'],
                        'voted_award_level' => $project['voted_award_level'],
                    ]);
            }
            if (!empty($candidateDefenseProjects)) {
                DB::table('candidate_defense_projects')->insert($candidateDefenseProjects);
            }
            $ret = $this->workflowSvc->endPhase(WorkflowService::PHASE_VOTE, $largeGroupId, $inTransaction);
            if (!$ret['ok']) {
                DB::rollBack();
                return [
                    'ok' => false,
                    'code' => 5,
                    'msg' => "结束投票阶段时出错。(code={$ret['code']}, msg={$ret['msg']})",
                ];
            }
            $ret = $this->workflowSvc->beginPhase(WorkflowService::PHASE_PREPARE_DEFENSE, $largeGroupId, $inTransaction);
            if (!$ret['ok']) {
                DB::rollBack();
                return [
                    'ok' => false,
                    'code' => 5,
                    'msg' => "开始准备答辩阶段时出错。(code={$ret['code']}, msg={$ret['msg']})",
                ];
            }
            $ret = $this->workflowSvc->beginPhase(WorkflowService::PHASE_AWARD, $largeGroupId, $inTransaction);
            if (!$ret['ok']) {
                DB::rollBack();
                return [
                    'ok' => false,
                    'code' => 5,
                    'msg' => "开始授奖阶段时出错。(code={$ret['code']}, msg={$ret['msg']})",
                ];
            }
            DB::commit();
            return [
                'ok' => true,
                'code' => 0,
                'msg' => 'ok',
            ];
        } catch (\Exception $e) {
            Log::error('', ['exception' => $e]);
            if ($inTransaction) {
                try {
                    DB::rollBack();
                } catch (\Exception $e2) {

                }
            }
            return [
                'ok' => false,
                'code' => 2,
                'msg' => '数据库错误',
            ];
        }
    }

    public function getManageLargeGroup5Data($largeGroupId)
    {
        $phaseId = WorkflowService::PHASE_DEFENSE;
        $statusIds = $this->workflowSvc->getProjectStatusesSincePhase($phaseId);
        $result = [
            'ok' => true,
            'code' => 0,
            'msg' => 'ok',
            'atPhaseMeetingReview' => $this->workflowSvc->atPhases([$phaseId], $largeGroupId),
            'hasPhaseMeetingReviewBegun' => $this->workflowSvc->hasPhaseBegun($phaseId, $largeGroupId),
            'hasPhaseMeetingReviewEnded' => $this->workflowSvc->hasPhaseEnded($phaseId, $largeGroupId),
            'projects' => DB::table('applications as a')
                ->where('a.large_group_id', $largeGroupId)
                ->whereIn('a.status_id', $statusIds)
                ->whereIn('final_award_level', ['特等奖', '一等奖'])
                ->leftJoin('users as b', 'a.user_id', '=', 'b.id')
                ->select('a.id', 'a.project_name', 'b.name as applicant', 'a.avg_score', 'a.ranking_percent', 'final_award_level')
                ->orderBy('ranking')
                ->get(),
        ];
        return $result;
    }

    public function leaderUpdateFinalAwardLevel($largeGroupId, $projectId, $finalAwardLevel)
    {
        $workflowSvc = new WorkflowService();
        if ($workflowSvc->notAtPhase(WorkflowService::PHASE_DEFENSE, $largeGroupId)) {
            return [
                'ok' => false,
                'code' => 3,
                'msg' => '现在不是会评阶段，不能执行该操作'
            ];
        }
        Project::where('id', $projectId)->update(['final_award_level' => $finalAwardLevel]);
        return [
            'ok' => true,
            'code' => 0,
            'msg' => 'ok',
        ];
    }

    public function committeeGetVote1Data($commissionerId, $largeGroupId)
    {
        $projects = DB::table('commissioner_vote_1 as a')
            ->where('a.commissioner_id', $commissionerId)
            ->where('a.large_group_id', $largeGroupId)
            ->leftJoin('applications as b', 'a.project_id', '=', 'b.id')
            ->leftJoin('users as c', 'b.user_id', '=', 'c.id')
            ->select('a.project_id as id', 'a.result as vote_result', 'a.remark as vote_remark',
                'b.project_name', 'b.avg_score', 'b.ranking', 'b.ranking_percent', 'b.planned_award_level',
                'c.name as applicant')
            ->orderBy('b.ranking')
            ->get();
        if (empty($projects)) {
            return [
                'ok' => true,
                'code' => 0,
                'msg' => 'ok',
                'projects' => [],
            ];
        }

        $projectIds = [];
        foreach ($projects as $project) {
            $projectIds[] = $project->id;
        }
        $reviewerGroupIds = ReviewerGroupProject::whereIn('project_id', $projectIds)
            ->where('score_status', ReviewerGroupProject::SCORE_STATUS_LEADER_REVIEW_PASSED)
            ->pluck('reviewer_group_id');
        $reviewerScoreProjects = ReviewerScoreProject::whereIn('reviewer_group_id', $reviewerGroupIds)
            ->select('project_id', 'score')
            ->get();
        $projectId2Scores = [];
        foreach ($reviewerScoreProjects as $reviewerScoreProject) {
            $projectId = $reviewerScoreProject->project_id;
            if (!isset($projectId2Scores[$projectId])) {
                $projectId2Scores[$projectId] = [];
            }
            $projectId2Scores[$projectId][] = $reviewerScoreProject->score;
        }
        foreach ($projectId2Scores as &$scores) {
            sort($scores);
        }
        unset($scores);
        foreach ($projects as $project) {
            $project->reviewer_scores = $projectId2Scores[$project->id];
        }
        return [
            'ok' => true,
            'code' => 0,
            'msg' => 'ok',
            'projects' => $projects,
        ];
    }

    /**
     * @param $commissionerId
     * @param $projectId
     * @param array $vote 模式:
     * - result: string enum, '同意'|'不同意'，必选
     * - remark: string，可选。理由
     * @return array
     * @throws \Exception
     */
    public function committeeVote1($commissionerId, $projectId, array $vote)
    {
        $largeGroupId = Project::where('id', $projectId)->value('large_group_id');
        $workflowSvc = new WorkflowService();
        if ($workflowSvc->notAtPhases([WorkflowService::PHASE_VOTE_1], $largeGroupId)) {
            return [
                'ok' => false,
                'code' => 3,
                'msg' => '现在不是待定区投票阶段，不能执行该操作'
            ];
        }
        if (empty($vote['result'])) {
            return [
                'ok' => false,
                'code' => 4,
                'msg' => "必选选择 同意 或 不同意",
            ];
        }
        if (!isset($vote['remark'])) {
            $vote['remark'] = '';
        }

        $inTransaction = false;
        try {
            DB::beginTransaction();
            $inTransaction = true;
            DB::table('commissioner_vote_1')
                ->where('project_id', $projectId)
                ->where('commissioner_id', $commissionerId)
                ->update([
                    'result' => $vote['result'],
                    'remark' => $vote['remark'],
                ]);
            $remainingVoteCount = DB::table('commissioner_vote_1')
                ->where('project_id', $projectId)
                ->where('result', Consts::VOTE_RESULT_UNVOTED)
                ->count();
            if ($remainingVoteCount == 0) {
                Project::where('id', $projectId)
                    ->update(['status_id' => Project::STATUS_VOTE_1_TO_SUBMIT_VOTE_RESULT]);
            }
            DB::commit();
            return [
                'ok' => true,
                'code' => 0,
                'msg' => 'ok',
            ];
        } catch (\Exception $e) {
            Log::error('', ['exception' => $e]);
            if ($inTransaction) {
                try {
                    DB::rollBack();
                } catch (\Exception $e2) {

                }
            }
            return [
                'ok' => false,
                'code' => 2,
                'msg' => '数据库错误',
            ];
        }
    }

    public function committeeGetVoteData($commissionerId, $largeGroupId)
    {
        $ret = (new ProjectService())->committeeGetVoteProjects($commissionerId, $largeGroupId);
        if (!$ret['ok']) {
            return [
                'ok' => false,
                'code' => 1,
                'msg' => '获取项目列表失败',
            ];
        }
        return [
            'ok' => true,
            'code' => 0,
            'msg' => 'ok',
            'projects' => $ret['projects'],
        ];
    }

    public function getManageLargeGroupDefenseData($largeGroupId)
    {
        $ret = (new ProjectService())->leaderGetDefenseProjects($largeGroupId);
        if (!$ret['ok']) {
            return [
                'ok' => false,
                'code' => 1,
                'msg' => '获取项目列表失败',
            ];
        }
        return [
            'ok' => true,
            'code' => 0,
            'msg' => 'ok',
            'large_group_id' => (int)$largeGroupId,
            'projects' => $ret['projects'],
        ];
    }

    public function updateDefenseAwardLevel($projectId, $defenseAwardLevel)
    {
        $project = Project::where('id', $projectId)->select('id', 'status_id', 'large_group_id', 'final_award_level')->first();
        $largeGroupId = $project->large_group_id;
        if ($this->workflowSvc->notAtPhase(WorkflowService::PHASE_DEFENSE, $largeGroupId)) {
            return [
                'ok' => false,
                'code' => 3,
                'msg' => '现在不是答辩阶段，不能执行该操作'
            ];
        }
        $expectedStatusId = Project::STATUS_DEFENSE_TO_REVIEW;
        if ($project->status_id !== $expectedStatusId) {
            return [
                'ok' => false,
                'code' => 4,
                'msg' => "项目当前状态不是 $expectedStatusId, 不能执行该操作"
            ];
        }
        Project::where('id', $projectId)->update(['final_award_level' => $defenseAwardLevel]);
        return [
            'ok' => true,
            'code' => 0,
            'msg' => 'ok',
        ];
    }

    public function leaderSubmitDefenseResult($largeGroupId)
    {
        $workflowSvc = new WorkflowService();
        if ($workflowSvc->notAtPhase(WorkflowService::PHASE_DEFENSE, $largeGroupId)) {
            return [
                'ok' => false,
                'code' => 3,
                'msg' => '现在不是答辩阶段，不能执行该操作'
            ];
        }

        $inTransaction = false;
        try {
            DB::beginTransaction();
            $inTransaction = true;
            Project::where('large_group_id', $largeGroupId)
                ->whereIn('status_id', [Project::STATUS_DEFENSE_TO_REVIEW])
                ->update(['status_id' => Project::STATUS_AWARD_AWARD]);
            $ret = $this->workflowSvc->endPhase(WorkflowService::PHASE_DEFENSE, $largeGroupId, $inTransaction);
            if (!$ret['ok']) {
                DB::rollBack();
                return [
                    'ok' => false,
                    'code' => 5,
                    'msg' => "结束答辩时出错。(code={$ret['code']}, msg={$ret['msg']})",
                ];
            }
            $ret = $this->workflowSvc->endPhase(WorkflowService::PHASE_AWARD, $largeGroupId, $inTransaction);
            if (!$ret['ok']) {
                DB::rollBack();
                return [
                    'ok' => false,
                    'code' => 5,
                    'msg' => "结束授奖时出错。(code={$ret['code']}, msg={$ret['msg']})",
                ];
            }
            DB::commit();
            return [
                'ok' => true,
                'code' => 0,
                'msg' => 'ok',
            ];
        } catch (\Exception $e) {
            Log::error('', ['exception' => $e]);
            if ($inTransaction) {
                try {
                    DB::rollBack();
                } catch (\Exception $e2) {

                }
            }
            return [
                'ok' => false,
                'code' => 2,
                'msg' => '数据库错误',
            ];
        }
    }

    /**
     * 配置奖项数量
     * @param int $largeGroupId
     * @param string $awardsConfigure
     * @return array
     * @author: lxy
     * @time: 2020/6/17 5:04 下午
     * @流程:
     */
    public function SetAwardsConfigure(int $largeGroupId, string $awardsConfigure): array
    {
        $awardsConfigure = json_decode($awardsConfigure);
        //查询大组信息
        $largeGroupInfo = DB::table('large_groups')->where('id', $largeGroupId)->select('id', 'attrs')->first();
        $attrs = json_decode($largeGroupInfo->attrs);
        $attrs->awards_configure = $awardsConfigure;
        $attrsUpdate = json_encode($attrs, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);

        DB::beginTransaction();
        try {
            //更新大组奖项配置信息
            $res = DB::table('large_groups')->where('id', $largeGroupId)->update(['attrs' => $attrsUpdate]);
            if (!is_numeric($res)) {
                throw new \Exception('数据库错误-奖项配置更新失败');
            }
            //分配奖项
            $res = $this->setProjectAward($largeGroupId, $awardsConfigure);
            if ($res !== true) {
                throw new \Exception($res);
            }

            DB::commit();
            return [
                'ok' => true,
                'msg' => '操作完成',
            ];

        } catch (\Exception $e) {
            DB::rollback();
            return [
                'ok' => false,
                'msg' => '数据库错误: ' . $e->getMessage(),
            ];
        }
    }

    private function setProjectAward(int $largeGroupId, array $awardsConfigure)
    {
        $allCount = 0;  // 获奖总数
        $meetingRankingThresholds = [];
        foreach ($awardsConfigure as $i => $awardConfigure) {
            if (!is_numeric($awardConfigure->number) || $awardConfigure->number < 1) {
                return "请先设置一等奖、二等奖、三等奖数量";
            }
            $allCount += $awardConfigure->number;
            $meetingRankingThresholds[$i] = $i > 0 ? $meetingRankingThresholds[$i - 1] + $awardConfigure->number : $awardConfigure->number;
        }

        // 查询项目信息
        $projectList = Project::where('large_group_id', $largeGroupId)
            ->where('status_id', Project::STATUS_MEETING_REVIEW_TO_REVIEW)
            ->where('meeting_ranking', '<=', $allCount)
            ->select('id', 'meeting_ranking')
            ->orderBy('meeting_ranking', 'asc')
            ->get();

        // 更新奖项sql
        $projectUpdateSql = 'update `applications` set award = case id  ';
        $projectIdsStr = '';
        $currentAwardLevelIndex = 0;
        foreach ($projectList as $item) {
            $projectId = $item->id;
            $projectIdsStr .= $projectId . ',';
            $awardTitle = $awardsConfigure[$currentAwardLevelIndex]->title;
            $projectUpdateSql .= "WHEN $projectId THEN '$awardTitle' ";
            if ($item->meeting_ranking == $meetingRankingThresholds[$currentAwardLevelIndex]) {
                $currentAwardLevelIndex++;
            }
        }

        // 重置项目授奖为null
        $res = DB::table('applications')
            ->where('large_group_id', $largeGroupId)
            ->where('status_id', Project::STATUS_MEETING_REVIEW_TO_REVIEW)
            ->update(['award' => null]);

        if (!is_numeric($res)) {
            return '数据库错误-项目奖项分配失败';
        }

        $projectIdsStr = rtrim($projectIdsStr, ',');
        $projectUpdateSql .= "END WHERE id IN ($projectIdsStr)";

        $res = DB::update($projectUpdateSql);
        if (is_numeric($res)) {
            return true;
        } else {
            return '数据库错误-项目奖项分配失败';
        }
    }


    /**
     * 设置项目会评排名
     * @param int $project_id
     * @param int $rank
     * @param int $largeGroupId
     * @return array
     * @author: lxy
     * @time: 2020/6/17 10:22 上午
     * @流程:
     */
    public function SetMeetingRank(int $project_id, int $rank, int $largeGroupId)
    {
        if ($rank < 1) {
            return [
                'ok' => false,
                'msg' => '排名异常',
            ];
        }
        //查询大组信息
        $largeGroupInfo = DB::table('large_groups')->where('id', $largeGroupId)->select('id', 'attrs')->first();
        $attrs = json_decode($largeGroupInfo->attrs);
        //获取项目信息
        $project = Project::find($project_id);
        $oldRank = (int)$project->meeting_ranking;
        $errorMessage = null;
        DB::beginTransaction();
        try {
            //更新项目排名
            $project->meeting_ranking = $rank;
            $res = $project->save();
            if (!$res) {
                throw new \Exception('数据库错误-更新排名失败');
            }

            if ($oldRank > $rank) {
                //排名上升 即 meeting_ranking 减小时
                //大于 $rank 小于 $oldRank 的项目 排名减1（meeting_ranking + 1）
                $res = Project::where('meeting_ranking', '>=', $rank)
                    ->where('meeting_ranking', '<', $oldRank)
                    ->where('id', '!=', $project_id)
                    ->where('large_group_id', $largeGroupId)
                    ->increment('meeting_ranking');

            } else {
                //排名下降 即 meeting_ranking 增大时
                //小于 $rank 大于 $oldRank 的项目 排名加1（meeting_ranking - 1）
                $res = Project::where('meeting_ranking', '<=', $rank)
                    ->where('meeting_ranking', '>', $oldRank)
                    ->where('id', '!=', $project_id)
                    ->where('large_group_id', $largeGroupId)
                    ->decrement('meeting_ranking');
            }

            if (!$res) {
                throw new \Exception('数据库错误-更新排名失败（2）');
            }

            //存在奖项配置时
            if (isset($attrs->awards_configure)) {
                $res = $this->setProjectAward($largeGroupId, $attrs->awards_configure);
                if ($res !== true) {
                    $errorMessage = $res;
                    throw new \Exception($res);
                }
            }

            DB::commit();
            return [
                'ok' => true,
                'msg' => '更新完成',
            ];

        } catch (\Exception $e) {
            Log::error('ManageLargeGroupService->SetMeetingRank() Failed to set meeting rank', ['exception' => $e]);
            DB::rollBack();
            if (empty($errorMessage)) {
                $errorMessage = '数据库错误';
            }
            return [
                'ok' => false,
                'msg' => $errorMessage,
            ];
        }
    }


    /**
     * 落选项目加入会评 | 入选项目移除会评
     * @param int $projectId
     * @param int $largeGroupId
     * @param string $remark
     * @param string $status
     * @return array
     * @author: lxy
     * @time: 2020/6/9 16:58 下午
     * @流程:
     */
    public function AddOrRemoveMeetingProject(int $projectId, int $largeGroupId, string $remark, string $status): array
    {
        //判断是否执行了DB操作 用于事务回滚
        $DbFlag = false;
        try {
            $this->verifyGroupWork($largeGroupId);

            if (empty($projectId)) {
                throw new \Exception('请选择操作项目');
            }

            if (empty($remark)) {
                throw new \Exception('请填写理由');
            }

            //查询项目信息 表 ReviewerGroupProject
            $projectInfo = ReviewerGroupProject::where('project_id', $projectId)->first();
            if (empty($projectInfo)) {
                throw new \Exception('项目不存在或状态异常');
            }

            DB::beginTransaction();
            //更新信息 表 reviewer_group_projects
            $projectInfo->large_group_leader_review_remark = $remark;
            $res = $projectInfo->save();
            if (!$res) {
                $DbFlag = true;
                throw new \Exception('数据库错误-录入理由失败');
            }

            //更新项目状态
            $res = Project::where('id', $projectId)->update(['status_id' => $status]);
            if (!is_numeric($res)) {
                $DbFlag = true;
                throw new \Exception('数据库错误-更新项目状态失败');
            }

            DB::commit();
            $result = [
                'ok' => true,
                'msg' => '操作完成'
            ];

        } catch (\Exception $e) {

            if ($DbFlag) {
                //执行了DB操作需要进行事务回滚
                DB::rollBack();
            }
            $result = [
                'ok' => false,
                'msg' => $e->getMessage()
            ];
        }

        return $result;
    }


    /**
     * 设置入选会评项目数量
     * @param int $largeGroupId
     * @param int $meetingNum
     * @return array
     * @author: lxy
     * @time: 2020/6/10 2:21 下午
     * @流程:
     */
    public function SetMeetingProjectNumber(int $largeGroupId, int $meetingNum): array
    {
        //判断是否执行了DB操作 用于事务回滚
        $DbFlag = false;
        try {
            $this->verifyGroupWork($largeGroupId);

            if (empty($largeGroupId) || empty($meetingNum)) {
                throw new \Exception('缺少必要信息');
            }

            //查询大组信息
            $largeGroup = DB::table('large_groups')->find($largeGroupId);
            if (empty($largeGroup)) {
                throw new \Exception('大组不存在或状态异常');
            }

            //查询晋级项目id
            $projectIds = $this->promotionProject($largeGroupId, $meetingNum);

            //将设定入选项目数录入大组信息
            $attrs = json_decode($largeGroup->attrs);
            $attrs->meeting = (object)[
                'project_num' => $meetingNum,
                'update_time' => date('Y-m-d H:i:s')
            ];
            $attrs = json_encode($attrs, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);

            //开启事务
            DB::beginTransaction();
            $res = DB::table('large_groups')
                ->where('id', $largeGroupId)
                ->update(['attrs' => $attrs]);
            if (!is_numeric($res)) {
                $DbFlag = true;
                throw new \Exception('入选项目设置失败');
            }

            //将晋级项目状态变更为 晋级 Project::STATUS_MEETING_REVIEW_TO_REVIEW
            $res = Project::whereIn('id', $projectIds)
                ->update(['status_id' => Project::STATUS_MEETING_REVIEW_TO_REVIEW]);
            if (!is_numeric($res)) {
                $DbFlag = true;
                throw new \Exception('更新项目状态失败');
            }

            //将未晋级项目状态变更为 落选 Project::STATUS_EXPERT_REVIEW_REJECTED
            DB::enableQueryLog();
            $res = Project::whereNotIn('id', $projectIds)
                ->where('large_group_id', $largeGroupId)
                ->whereIn('status_id', [
                    Project::STATUS_EXPERT_REVIEW_SCORE_FINISHED,
                    Project::STATUS_EXPERT_REVIEW_REJECTED,
                    Project::STATUS_MEETING_REVIEW_TO_REVIEW])
                ->update(['status_id' => Project::STATUS_EXPERT_REVIEW_REJECTED]);
            if (!is_numeric($res)) {
                $DbFlag = true;
                throw new \Exception('更新项目状态失败');
            }

            DB::commit();
            $result = [
                'ok' => true,
                'msg' => '操作完成'
            ];

        } catch (\Exception $e) {
            if ($DbFlag) {
                //执行了DB操作需要进行事务回滚
                DB::rollBack();
            }
            $result = [
                'ok' => false,
                'msg' => $e->getMessage()
            ];
        }
        return $result;
    }


    /**
     * 检测大组当前状态是否专家评审阶段、是否存在未评审完项目
     * @param int $largeGroupId
     * @author: lxy
     * @time: 2020/6/10 3:37 下午
     * @流程:
     * @throws \Exception
     */
    private function verifyGroupWork(int $largeGroupId)
    {
        /**
         * 检测当前阶段
         */
        if ($this->workflowSvc->notAtPhases([WorkflowService::PHASE_EXPERT_REVIEW], $largeGroupId)) {
            throw new \Exception('现在不是专家评审阶段，不能执行该操作');
        }

        /**
         * @edit
         * @time 2020/6/10
         * @需求：STATUS_EXPERT_REVIEW_TO_LEADER_REVIEW 状态已废弃
         * 网评阶段状态分为：
         * STATUS_EXPERT_REVIEW_TO_SCORE        待评分
         * STATUS_EXPERT_REVIEW_SCORE_FINISHED  已评分
         * STATUS_EXPERT_REVIEW_REJECTED        落选会评
         */
        $scoreUnfinishedProjectCount = Project::where('large_group_id', $largeGroupId)
            ->where('status_id', Project::STATUS_EXPERT_REVIEW_TO_SCORE)
            ->count();
        if ($scoreUnfinishedProjectCount > 0) {
            throw new \Exception("还有 $scoreUnfinishedProjectCount 个项目未完成专家评审，不能执行该操作");
        }
    }


    /**
     * 根据大组长设定入选项目数量查询项目
     * @param int $largeGroupId
     * @param int $meetingNum
     * @return array
     * @author: lxy
     * @time: 2020/6/10 3:07 下午
     * @流程:
     */
    private function promotionProject(int $largeGroupId, int $meetingNum): array
    {
        //查询晋级项目信息（按平均分逆序查询 "$meetingNum" 条数据）
        $projectList = ReviewerGroupProject::where('large_group_id', $largeGroupId)
            ->orderBy('avg_score', 'desc')
            ->select('project_id')
            ->limit($meetingNum)
            ->get();
        $ids = [];
        foreach ($projectList as $item) {
            $ids[] = $item->project_id;
        }
        return $ids;
    }

}
